/*
-------------------------------------------------------------------------------
Author: Joseph Lamoree <jlamoree@gmail.com>
Date: Tue Aug 01 10:47:58 PDT 2006
Description:
  Create the user, database, and structure for MachBlog on PostgreSQL
-------------------------------------------------------------------------------

These must be executed by a user with permission to create a user and database.

Option 1)
  From the console, execute the following (modifying where appropriate):

  PGHOME=/Library/PostgreSQL8
  PGDATA=$PGHOME/data
  PGPORT=5432
  PGUSER=postgres
  PGHOSTADDR=127.0.0.1
  export PGHOME PGDATA PGPORT PGUSER PGHOSTADDR
  eval $PGHOME/bin/createuser --no-adduser --no-createdb --encrypted machblog
  eval $PGHOME/bin/createdb --encoding=UNICODE --owner=machblog machblog
  eval $PGHOME/bin/psql -U machblog -f machblog_postgresql.sql machblog

  # To remove the MachBlog user and database
  # eval $PGHOME/bin/dropdb machblog
  # eval $PGHOME/bin/dropuser machblog

Option 2)
  Connect to the database with psql and execute these statements. Disconnect
  before executing the rest of the script.

  CREATE USER machblog NOCREATEDB NOCREATEUSER ENCRYPTED PASSWORD 'abc123';
  CREATE DATABASE machblog WITH ENCODING = 'UNICODE' OWNER = machblog;

To start over:

  DROP TABLE machblog_aggregator;
  DROP TABLE machblog_blocked_ip;
  DROP TABLE machblog_comment;
  DROP TABLE machblog_entry_category;
  DROP TABLE machblog_entry;
  DROP TABLE machblog_category;
  DROP TABLE machblog_subscriber;
  DROP TABLE machblog_trackback;
  DROP TABLE machblog_user;

*/

-- Set or change the password for the MachBlog user
ALTER USER machblog ENCRYPTED PASSWORD 'abc123';

CREATE OR REPLACE FUNCTION drop_table_if_exists (VARCHAR(255)) RETURNS VOID AS '
BEGIN
  IF (SELECT COUNT(*) FROM pg_tables WHERE tablename = $1) THEN
	EXECUTE ''DROP TABLE '' || $1 || '' CASCADE'';
  END IF;
  RETURN;
END;'
LANGUAGE 'plpgsql';

BEGIN TRANSACTION;

-- SELECT drop_table_if_exists('machblog_user');
CREATE TABLE machblog_user (
  user_id                                       CHAR(35) NOT NULL,
  email                                         VARCHAR(100) DEFAULT '' NOT NULL,
  password                                      VARCHAR(50) DEFAULT '' NOT NULL,
  password_salt                                 CHAR(35),
  first_name                                    VARCHAR(50) DEFAULT '' NOT NULL,
  last_name                                     VARCHAR(50) DEFAULT '' NOT NULL,
  dt_last_access                                BIGINT,
  ip_last_access                                VARCHAR(15),
  created_by_id                                 CHAR(35) NOT NULL,
  dt_created                                    BIGINT,
  ip_created                                    VARCHAR(15) NOT NULL,
  modified_by_id                                CHAR(35),
  dt_modified                                   BIGINT,
  ip_modified                                   VARCHAR(15),
  is_active                                     SMALLINT DEFAULT 0 NOT NULL,
  CONSTRAINT machblog_user_pk PRIMARY KEY (user_id),
  CONSTRAINT machblog_user_unique_email UNIQUE (email),
  CONSTRAINT machblog_user_fk_created_by FOREIGN KEY (created_by_id) REFERENCES machblog_user (user_id),
  CONSTRAINT machblog_user_fk_modified_by FOREIGN KEY (modified_by_id) REFERENCES machblog_user (user_id)
);
CREATE INDEX machblog_user_ix_created_by ON machblog_user (created_by_id);
CREATE INDEX machblog_user_ix_modified_by ON machblog_user (modified_by_id);


-- SELECT drop_table_if_exists('machblog_entry');
CREATE TABLE machblog_entry (
  entry_id                                      CHAR(35) NOT NULL,
  title                                         VARCHAR(100) DEFAULT '' NOT NULL,
  body                                          TEXT NOT NULL,
  more_body                                     TEXT,
  allow_comments                                SMALLINT DEFAULT 0,
  allow_trackbacks                              SMALLINT DEFAULT 0,
  dt_posted                                     BIGINT,
  photo                                         VARCHAR(255),
  enclosure                                     VARCHAR(255),
  enclosure_mime_type                           VARCHAR(255),
  enclosure_size                                BIGINT,
  num_views                                     INTEGER DEFAULT 0 NOT NULL,
  created_by_id                                 CHAR(35) DEFAULT '' NOT NULL,
  dt_created                                    BIGINT,
  ip_created                                    VARCHAR(15) DEFAULT '' NOT NULL,
  modified_by_id                                CHAR(35),
  dt_modified                                   BIGINT,
  ip_modified                                   VARCHAR(15),
  subscriber_email_sent                         SMALLINT,
  is_hidden                                     SMALLINT DEFAULT 0 NOT NULL,
  is_active                                     SMALLINT DEFAULT 0 NOT NULL,
  CONSTRAINT machblog_entry_pk PRIMARY KEY (entry_id),
  CONSTRAINT machblog_entry_fk_created_by FOREIGN KEY (created_by_id) REFERENCES machblog_user (user_id),
  CONSTRAINT machblog_entry_fk_modified_by FOREIGN KEY (modified_by_id) REFERENCES machblog_user (user_id)
);
CREATE INDEX machblog_entry_ix_dt_posted ON machblog_entry (dt_posted);

/*
  -- Adding missing subscriber_email_sent column
  ALTER TABLE machblog_entry ADD subscriber_email_sent SMALLINT;
  UPDATE machblog_entry SET subscriber_email_sent = 0;
  ALTER TABLE machblog_entry ALTER COLUMN subscriber_email_sent SET DEFAULT(0);
  ALTER TABLE machblog_entry ALTER COLUMN subscriber_email_sent SET NOT NULL;

  -- Adding new column for is_hidden
  ALTER TABLE machblog_entry ADD is_hidden SMALLINT;
  UPDATE machblog_entry SET is_hidden = 0;
  ALTER TABLE machblog_entry ALTER COLUMN is_hidden SET DEFAULT(0);
  ALTER TABLE machblog_entry ALTER COLUMN is_hidden SET NOT NULL;

*/

-- SELECT drop_table_if_exists('machblog_aggregator');
CREATE TABLE machblog_aggregator (
  aggregator_id                                 CHAR(35) NOT NULL,
  aggregator_name                               VARCHAR(100) NOT NULL,
  aggregator_description                        VARCHAR(500),
  aggregator_url                                VARCHAR(100) NOT NULL,
  ping_url                                      VARCHAR(250) NOT NULL,
  auto_ping                                     SMALLINT DEFAULT 0 NOT NULL,
  dt_last_pinged                                BIGINT,
  created_by_id                                 CHAR(35) NOT NULL,
  dt_created                                    BIGINT,
  ip_created                                    VARCHAR(15) NOT NULL,
  modified_by_id                                CHAR(35),
  dt_modified                                   BIGINT,
  ip_modified                                   VARCHAR(15),
  is_active                                     SMALLINT DEFAULT 0 NOT NULL,
  CONSTRAINT machblog_aggregator_pk PRIMARY KEY (aggregator_id),
  CONSTRAINT machblog_aggregator_fk_created_by FOREIGN KEY (created_by_id) REFERENCES machblog_user (user_id),
  CONSTRAINT machblog_aggregator_fk_modified_by FOREIGN KEY (modified_by_id) REFERENCES machblog_user (user_id)
);


-- SELECT drop_table_if_exists('machblog_blocked_ip');
CREATE TABLE machblog_blocked_ip (
  blocked_ip                                    VARCHAR(15) NOT NULL,
  created_by_id                                 CHAR(35) NOT NULL,
  dt_created                                    BIGINT,
  ip_created                                    VARCHAR(15) DEFAULT '' NOT NULL,
  CONSTRAINT machblog_blocked_ip_pk PRIMARY KEY (blocked_ip),
  CONSTRAINT machblog_blocked_ip_fk_created_by FOREIGN KEY (created_by_id) REFERENCES machblog_user (user_id)
);


-- SELECT drop_table_if_exists('machblog_category');
CREATE TABLE machblog_category (
  category_id                                   CHAR(35) NOT NULL,
  category_name                                 VARCHAR(50) NOT NULL,
  created_by_id                                 CHAR(35) NOT NULL,
  dt_created                                    BIGINT,
  ip_created                                    VARCHAR(15) DEFAULT '' NOT NULL,
  modified_by_id                                CHAR(35),
  dt_modified                                   BIGINT,
  ip_modified                                   VARCHAR(15),
  is_active                                     SMALLINT DEFAULT 0 NOT NULL,
  CONSTRAINT machblog_category_pk PRIMARY KEY (category_id),
  CONSTRAINT machblog_category_unique_category_name UNIQUE (category_name),
  CONSTRAINT machblog_category_fk_created_by FOREIGN KEY (created_by_id) REFERENCES machblog_user (user_id),
  CONSTRAINT machblog_category_fk_modified_by FOREIGN KEY (modified_by_id) REFERENCES machblog_user (user_id)
);


-- SELECT drop_table_if_exists('machblog_comment');
CREATE TABLE machblog_comment (
  comment_id                                    CHAR(35) NOT NULL,
  entry_id                                      CHAR(35) NOT NULL,
  name                                          VARCHAR(100),
  email                                         VARCHAR(100),
  url                                           VARCHAR(250),
  comment                                       TEXT,
  is_subscribed                                 SMALLINT DEFAULT 0 NOT NULL,
  subscription_only                             SMALLINT,
  subscriber_email_sent                         SMALLINT,
  created_by_id                                 CHAR(35),
  dt_created                                    BIGINT,
  ip_created                                    VARCHAR(15) DEFAULT '' NOT NULL,
  modified_by_id                                CHAR(35),
  dt_modified                                   BIGINT,
  ip_modified                                   VARCHAR(15),
  is_active                                     SMALLINT DEFAULT 0 NOT NULL,
  CONSTRAINT machblog_comment_pk PRIMARY KEY (comment_id),
  CONSTRAINT machblog_comment_fk_entry FOREIGN KEY (entry_id) REFERENCES machblog_entry (entry_id),
  CONSTRAINT machblog_comment_fk_created_by FOREIGN KEY (created_by_id) REFERENCES machblog_user (user_id),
  CONSTRAINT machblog_comment_fk_modified_by FOREIGN KEY (modified_by_id) REFERENCES machblog_user (user_id)
);


-- SELECT drop_table_if_exists('machblog_entry_category');
CREATE TABLE machblog_entry_category (
  entry_id                                      CHAR(35) NOT NULL,
  category_id                                   CHAR(35) NOT NULL,
  CONSTRAINT machblog_entry_category_pk PRIMARY KEY (entry_id, category_id),
  CONSTRAINT machblog_entry_category_fk_entry FOREIGN KEY (entry_id) REFERENCES machblog_entry (entry_id),
  CONSTRAINT machblog_entry_category_fk_category FOREIGN KEY (category_id) REFERENCES machblog_category (category_id)
);


-- SELECT drop_table_if_exists('machblog_subscriber');
CREATE TABLE machblog_subscriber (
  subscriber_id                                 CHAR(35) NOT NULL,
  subscriber_email                              VARCHAR(100) DEFAULT '' NOT NULL,
  verify_id                                     CHAR(35),
  created_by_id                                 CHAR(35),
  dt_created                                    BIGINT,
  ip_created                                    VARCHAR(15) DEFAULT '' NOT NULL,
  modified_by_id                                CHAR(35),
  dt_modified                                   BIGINT,
  ip_modified                                   VARCHAR(15),
  is_active                                     SMALLINT DEFAULT 0 NOT NULL,
  CONSTRAINT machblog_subscriber_pk PRIMARY KEY (subscriber_id),
  CONSTRAINT machblog_subscriber_unique_email UNIQUE (subscriber_email)
);
CREATE INDEX machblog_subscriber_ix_created_by ON machblog_subscriber (created_by_id);
CREATE INDEX machblog_subscriber_ix_modified_by ON machblog_subscriber (modified_by_id);


-- SELECT drop_table_if_exists('machblog_trackback');
CREATE TABLE machblog_trackback (
  trackback_id                                  CHAR(35) NOT NULL,
  trackback_title                               VARCHAR(50),
  trackback_excerpt                             TEXT,
  trackback_url                                 VARCHAR(500) NOT NULL,
  trackback_blog_name                           VARCHAR(250),
  entry_id                                      CHAR(35) NOT NULL,
  created_by_id                                 CHAR(35) NOT NULL,
  dt_created                                    BIGINT,
  ip_created                                    VARCHAR(15) NOT NULL,
  modified_by_id                                CHAR(35),
  dt_modified                                   BIGINT,
  ip_modified                                   VARCHAR(15),
  is_active                                     SMALLINT DEFAULT 0 NOT NULL,
  CONSTRAINT machblog_trackback_pk PRIMARY KEY (trackback_id),
  CONSTRAINT machblog_trackback_fk_entry FOREIGN KEY (entry_id) REFERENCES machblog_entry (entry_id),
  CONSTRAINT machblog_trackback_fk_created_by FOREIGN KEY (created_by_id) REFERENCES machblog_user (user_id),
  CONSTRAINT machblog_trackback_fk_modified_by FOREIGN KEY (modified_by_id) REFERENCES machblog_user (user_id)
);
CREATE INDEX machblog_trackback_ix_entry ON machblog_trackback (entry_id);
CREATE INDEX machblog_trackback_ix_created_by ON machblog_trackback (created_by_id);
CREATE INDEX machblog_trackback_ix_modified_by ON machblog_trackback (modified_by_id);


INSERT INTO machblog_user (
  user_id,
  email,
  password,
  password_salt,
  first_name,
  last_name,

  created_by_id,
  dt_created,
  ip_created,

  modified_by_id,
  dt_modified,
  ip_modified,

  dt_last_access,
  ip_last_access,
  is_active
) VALUES (
  'F2D2EE50-1111-1927-82EDC2833CAC6792',
  'admin@admin.com',
  'F4CB632BBD5D93F18AE1F2723CBF6D63C188B4B8',
  '3BD04BF0-1111-1927-829D2D609000BD42',
  'Default',
  'User',

  'F2D2EE50-1111-1927-82EDC2833CAC6792',
  0,
  '127.0.0.1',

  'F2D2EE50-1111-1927-82EDC2833CAC6792',
  0,
  '127.0.0.1',

  0,
  '127.0.0.1',
  1
);

END TRANSACTION;
